package com.ztc.ZtcTransportCapitalLog.dao;

import com.aqgj.util.BaseDaoImpl;
import com.aqgj.util.Pager;
import com.ztc.ZtcTransportCapitalLog.bean.ZtcTransportCapitalLog;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository("ZtcTransportCapitalLogDao")
public class ZtcTransportCapitalLogDao extends BaseDaoImpl<ZtcTransportCapitalLog> {

    /** 获取当前公司收支明细 -时间段搜索 */
    public Pager<HashMap<String,Object>> searchZtcTransportCapitalLog(String transportId, Integer type, Integer pageNumber, Integer pageSize, String startDate, String endDate,String fullName) {
        Pager<HashMap<String,Object>> pager=new Pager<>();
        pager.setPageSize(pageSize);
        pager.setCurrentPage(pageNumber);
        String sql="select z.id,z.transport_id,z.type,z.money,z.after_balance,z.business_id,z.remark,z.create_time from ztc_transport_capital_log z where z.transport_id='"+transportId+"' ";
        if(startDate!=null&&!"".equals(startDate)&&endDate!=null&&!"".equals(endDate)){
            sql+="and (z.create_time>='"+startDate+"'  and z.create_time<='"+endDate+"') ";
        }
        if(type==0){
            sql="select z.id,z.transport_id,z.type,z.money,z.after_balance,z.business_id,z.remark,z.create_time from ztc_transport_capital_log z,ztc_transport c where z.transport_id=c.id ";
            if(fullName!=null&&!"".equals(fullName)){
                sql+=" and c.full_name like '%"+fullName+"%' ";
            }
            if(startDate!=null&&!"".equals(startDate)&&endDate!=null&&!"".equals(endDate)){
                sql+="and (z.create_time>='"+startDate+"' and z.create_time<='"+endDate+"') ";
            }
        }
        sql+="order by z.create_time desc";
        HashMap<String,Object> map= this.listAndSumSqlPagQuery(sql,pageNumber,pageSize);
        pager.setRecordTotal(Integer.parseInt(map.get("sum").toString()));
        String [] stringArrays=new String[]{"id","transportId","type","money","afterBalance","businessId","remark","createTime"};
        pager.setContent(this.getKeyList((List<Object[]>) map.get("data"),stringArrays));
        return pager;
    }

    public List<Object[]> getAllCapitalLog(String transportId,Integer type,String fullName,String startDate,String endDate) {
        String sql="select z.id,z.transport_id,z.type,z.money,z.after_balance,z.business_id,z.remark,z.create_time from ztc_transport_capital_log z where z.transport_id='"+transportId+"' ";
        if(startDate!=null&&!"".equals(startDate)&&endDate!=null&&!"".equals(endDate)){
            sql+="and (z.create_time>='"+startDate+"'  and z.create_time<='"+endDate+"') ";
        }
        if(type==0){
            sql="select z.id,z.transport_id,z.type,z.money,z.after_balance,z.business_id,z.remark,z.create_time from ztc_transport_capital_log z,ztc_transport c where z.transport_id=c.id ";
            if(fullName!=null&&!"".equals(fullName)){
                sql+=" and c.full_name like '%"+fullName+"%' ";
            }
            if(startDate!=null&&!"".equals(startDate)&&endDate!=null&&!"".equals(endDate)){
                sql+=" and (z.create_time>='"+startDate+"' and z.create_time<='"+endDate+"') ";
            }
        }
        sql+=" order by z.create_time desc";
        return  this.currentSession().createNativeQuery(sql).getResultList();
    }

    public List<ZtcTransportCapitalLog> getTransportCapitalLogAndTransportById(String businessId,boolean isRefundPenalty){
        String sql = " select * from ztc_transport_capital_log c inner join ztc_transport t on c.transport_id = t.id" +
                " where c.business_id = ? and c.type BETWEEN ? and ?";
        if(isRefundPenalty){
            return this.currentSession().createNativeQuery(sql,ZtcTransportCapitalLog.class).setParameter(1,businessId).setParameter(2,11).setParameter(3,14).getResultList();
        }
        return this.currentSession().createNativeQuery(sql,ZtcTransportCapitalLog.class).setParameter(1,businessId).setParameter(2,3).setParameter(3,6).getResultList();
    }

    /**
     * 根据公司ID获取最新N条收益信息.
     * @param tid  公司ID
     * @param number 条数(6)
     * @return
     */
    public List<Map> getUpCapitalLogListByTid(String tid,Integer number){
        String sql = " SELECT CASE z.`type`  " +
                " WHEN 1 THEN '提现' " +
                " WHEN 2 THEN '提现失败'  " +
                " WHEN 3 THEN '购票进账' " +
                " WHEN 4 THEN '购票进账' " +
                " WHEN 5 THEN '购票进账' " +
                " WHEN 6 THEN '购票进账'  " +
                " WHEN 7 THEN '退票出账'  " +
                " WHEN 8 THEN '退票出账'  " +
                " WHEN 9 THEN '退票出账'  " +
                " WHEN 10 THEN '退票出账'  " +
                " WHEN 11 THEN '违约金进账' " +
                " WHEN 12 THEN '违约金进账' " +
                " WHEN 13 THEN '违约金进账'  " +
                " WHEN 14 THEN '违约金进账'  " +
                " WHEN 15 THEN '违约金出账'  " +
                " WHEN 16 THEN '违约金出账'  " +
                " WHEN 17 THEN '违约金出账' " +
                " WHEN 18 THEN '违约金出账' END AS type " +
                ",z.money,z.create_time,if(z.`type` in (2,3,4,5,6,11,12,13,14),1,0) isExit " +
                "FROM ztc_transport_capital_log z where z.transport_id=? order by z.create_time desc limit ? ";
        return this.currentSession().createNativeQuery(sql).setParameter(1,tid).setParameter(2,number).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
    }

    /**
     * 根据年份获取该年度每月收益总额.
     * @param tid  公司ID
     * @param year  年份
     * @return  每月收益额map
     */
    public Map getCapitalTotalByTid(String tid,Integer year){
        String sql="select  " +
                "ifnull(sum(case month(z.create_time) when '1' then z.money else 0 end),0) as jan,  " +
                "ifnull(sum(case month(z.create_time) when '2' then z.money else 0 end),0) as feb,  " +
                "ifnull(sum(case month(z.create_time) when '3' then z.money else 0 end),0) as mar,  " +
                "ifnull(sum(case month(z.create_time) when '4' then z.money else 0 end),0) as apr,  " +
                "ifnull(sum(case month(z.create_time) when '5' then z.money else 0 end),0) as may,  " +
                "ifnull(sum(case month(z.create_time) when '6' then z.money else 0 end),0) as jun,  " +
                "ifnull(sum(case month(z.create_time) when '7' then z.money else 0 end),0) as jul,  " +
                "ifnull(sum(case month(z.create_time) when '8' then z.money else 0 end),0) as aug,  " +
                "ifnull(sum(case month(z.create_time) when '9' then z.money else 0 end),0) as sep,  " +
                "ifnull(sum(case month(z.create_time) when '10' then z.money else 0 end),0) as octo,  " +
                "ifnull(sum(case month(z.create_time) when '11' then z.money else 0 end),0) as nov, " +
                "ifnull(sum(case month(z.create_time) when '12' then z.money else 0 end),0) as dece " +
                "from ztc_transport_capital_log z  " +
                "where year(z.create_time)=?  and z.transport_id=? ";
        return (Map)this.currentSession().createNativeQuery(sql).setParameter(1,year).setParameter(2,tid).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList().get(0);
    }

}